﻿using Microsoft.Data.SqlClient;
using Password_Manager.EncryptedFile;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Password_Manager.Database
{

    internal class CreateDatabaseAndTables
    {
        private string connectionString = ConfigurationManager.ConnectionStrings["SqlServerConnection"].ConnectionString;

        public void CreateTable() //测试连接
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("数据库连接失败，请检查连接字符串！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }



        public async Task<List<Table>> GetTables(int account)
        {
            SimpleXmlHelper simple = new SimpleXmlHelper();
            var products = new List<Table>();

            var connectionString = ConfigurationManager.ConnectionStrings["SqlServerConnection"].ConnectionString;

            using (var connection = new SqlConnection(connectionString))
            {
                const string query = "SELECT LdentificationCode FROM KeyTable where Account = @Account";

                using (var command = new SqlCommand(query, connection))
                {

                    command.Parameters.AddWithValue("@Account", account);

                    await connection.OpenAsync(); //异步打开连接

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            //var code = reader.GetString("LdentificationCode");
                            var code = reader.GetString(0);

                            var fileItem = simple.FindFileById(code);

                            if (fileItem != null && !string.IsNullOrEmpty(fileItem.Encryption))
                            {
                                products.Add(new Table
                                {
                                    LdentificationCode = code,
                                    Account = account,
                                    Encryption = fileItem.Encryption
                                });
                            }
                        }
                    }
                }
            }
            MessageBox.Show($"查询完成，共处理 {products.Count} 条数据");
            return products;
        }

        //用户登录
        public void UserLogin(string account, string password)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT COUNT(*) FROM LoginTable WHERE Account = @Account AND Password = @Password";
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Account", account);
                        command.Parameters.AddWithValue("@Password", password);
                        int count = (int)command.ExecuteScalar();
                        if (count > 0)
                        {
                            MessageBox.Show("登录成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            ManagerInterface manager = new ManagerInterface(int.Parse(account));
                            manager.ShowDialog(); // 显示主界面


                        }
                        else
                        {
                            MessageBox.Show("账号或密码错误！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        //用户注册
        public void UserRegistration(string account, string password)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "INSERT INTO LoginTable (Account, Password) VALUES (@Account, @Password)";
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Account", account);
                        command.Parameters.AddWithValue("@Password", password);
                        command.ExecuteNonQuery();
                        MessageBox.Show("注册成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show($"注册失败，请检查账号是否已存在！错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        //保存密码
        public void SavePassword(int id, int account, string key, string uniqueId)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "INSERT INTO KeyTable (id,Account,SecretKey,LdentificationCode) VALUES (@id,@Account, @SecretKey, @LdentificationCode)";
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@id", id);

                        command.Parameters.AddWithValue("@Account", account);

                        command.Parameters.AddWithValue("@SecretKey", key);

                        command.Parameters.AddWithValue("@LdentificationCode", uniqueId);


                        command.ExecuteNonQuery();
                        MessageBox.Show("密钥保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        //自增长密钥序号
        public int GetNextAccountId()
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT ISNULL(MAX(id), 0) + 1 FROM KeyTable";
                    using (var command = new SqlCommand(query, connection))
                    {
                        return (int)command.ExecuteScalar();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return -1; // 返回-1表示出错
            }
        }

        //获取密钥
        public string GetSecretKey(int Account, string LdentificationCode)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "SELECT SecretKey FROM KeyTable WHERE Account = @Account and LdentificationCode = @LdentificationCode";
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@Account", Account);

                        command.Parameters.AddWithValue("@LdentificationCode", LdentificationCode);

                        var result = command.ExecuteScalar();
                        if (result != null)
                        {
                            return result.ToString();
                        }
                        else
                        {
                            MessageBox.Show("未找到密钥！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }
        //通过识别码删除密钥
        public void DeleteTable(string code)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                const string sql = "DELETE FROM KeyTable WHERE LdentificationCode = @Code";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.Parameters.AddWithValue("@Code", code);

                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
        }
        //修改用户id
        public void UpdateUserId(int newAccount, string ldentificationCode)
        {
            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    string query = "UPDATE KeyTable SET Account = @NewAccount WHERE LdentificationCode = @Code";
                    using (var command = new SqlCommand(query, connection))
                    {
                        command.Parameters.AddWithValue("@NewAccount", newAccount);
                        command.Parameters.AddWithValue("@Code", ldentificationCode);
                        command.ExecuteNonQuery();
                        MessageBox.Show("用户ID更新成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"错误：{ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}


